package cn.edu.nju.dapenti;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class DatabaseUtil {
	private static DatabaseUtil databaseUtil;
	DatabaseHelper databaseHelper;
	private SQLiteDatabase db;
	
	private RSSFeed getFeedInfo (int feedid) {
		if (db == null || !db.isOpen()) db = databaseHelper.getReadableDatabase();
		Cursor c= db.rawQuery("SELECT * FROM feed WHERE _id = ?", new String[]{String.valueOf(feedid)});
		RSSFeed feed = null;
		c.moveToFirst();
		if (!c.isAfterLast()) {
			feed = new RSSFeed();
			feed.setId(c.getInt(0)); 
			feed.setTitle(c.getString(1)); 
			feed.setPubdate(c.getString(2));
			feed.setUrl(c.getString(3));
			feed.setName(c.getString(4));
	    }
		c.close();
		if (db.isOpen()) db.close();
		return feed;
	}
	
	public RSSFeed getFeed (int feedid) {
		return getFeed(0, Settings.MAXCOUNT, feedid);
	}
	
	public RSSFeed getFeed (int itemStart, int itemCount, int feedid) {
		RSSFeed feed = getFeedInfo(feedid);
		getItemInFeed(feed, itemStart, itemCount);
		return feed;
	}
	
	public RSSFeed getNewFeedItems (String refreshDate, int itemCount, int feedid) {
		RSSFeed feed = getFeedInfo(feedid);
		getNewItemsInFeed(feed, refreshDate, itemCount);
		return feed;
	}
	
	public RSSFeed getOldFeedItems (String moreDate, int itemCount, int feedid) {
		RSSFeed feed = getFeedInfo(feedid);
		getOldItemsInFeed(feed, moreDate, itemCount);
		return feed;
	}
	
	private void getOldItemsInFeed (RSSFeed feed, String moreDate, int itemCount) {
		if (feed == null) return;
		if (db == null || !db.isOpen()) db = databaseHelper.getReadableDatabase();
		Cursor c= db.rawQuery("select * from item where _feedid = ? and _pubdate < ? order by _pubdate desc limit ?,?", new String[]{String.valueOf(feed.getId()), moreDate, String.valueOf(0), String.valueOf(itemCount)});
		RSSItem item;
		c.moveToFirst();
		while (!c.isAfterLast()) {
			item = new RSSItem();
			setItem(item, c);
			feed.addItem(item);
			c.moveToNext();
	    }
		c.close();
		if (db.isOpen()) db.close();
		return;
	}
	
	private void getNewItemsInFeed (RSSFeed feed, String refreshDate, int itemCount) {
		if (feed == null) return;
		if (db == null || !db.isOpen()) db = databaseHelper.getReadableDatabase();
		Cursor c= db.rawQuery("select * from item where _feedid = ? and _pubdate > ? order by _pubdate desc limit ?,?", new String[]{String.valueOf(feed.getId()), refreshDate, String.valueOf(0), String.valueOf(itemCount)});
		RSSItem item;
		c.moveToFirst();
		while (!c.isAfterLast()) {
			item = new RSSItem();
			setItem(item, c);
			feed.addItem(item);
			c.moveToNext();
	    }
		c.close();
		if (db.isOpen()) db.close();
		return;
	}
	
	private void getItemInFeed (RSSFeed feed, int start, int itemCount) {
		if (feed == null) return;
		if (db == null || !db.isOpen()) db = databaseHelper.getReadableDatabase();
		Cursor c= db.rawQuery("select * from item where _feedid = ? order by _pubdate desc limit ?,?", new String[]{String.valueOf(feed.getId()), String.valueOf(start), String.valueOf(itemCount)});
		RSSItem item;
		c.moveToFirst();
		while (!c.isAfterLast()) {
			item = new RSSItem();
			setItem(item, c);
			feed.addItem(item);
			c.moveToNext();
	    }
		c.close();
		if (db.isOpen()) db.close();
		return;
	}
	
	public RSSItem getItem (int itemid) {
		if (db == null || !db.isOpen()) db = databaseHelper.getReadableDatabase();
		Cursor c= db.rawQuery("select * from item where _id = ? ", new String[]{String.valueOf(itemid)});
		RSSItem item = new RSSItem();
		c.moveToFirst();
		if (!c.isAfterLast()) {
			setItem(item, c);
	    }
		c.close();
		if (db.isOpen()) db.close();
		return item;
	}
	
	public RSSItem getNextItem (String curPubdate, int feedid) {
		if (db == null || !db.isOpen()) db = databaseHelper.getReadableDatabase();
		Cursor c= db.rawQuery("select * from item where _feedid = ? and _pubdate > ? order by _pubdate limit 1", new String[]{String.valueOf(feedid), curPubdate});
		RSSItem item = new RSSItem();;
		c.moveToFirst();
		if (!c.isAfterLast()) {
			setItem(item, c);
	    }
		c.close();
		if (db.isOpen()) db.close();
		return item;
	}
	
	public RSSItem getPreItem (String curPubdate, int feedid) {
		if (db == null || !db.isOpen()) db = databaseHelper.getReadableDatabase();
		Cursor c= db.rawQuery("select * from item where _feedid = ? and _pubdate < ? order by _pubdate desc limit 1", new String[]{String.valueOf(feedid), curPubdate});
		RSSItem item = new RSSItem();;
		c.moveToFirst();
		if (!c.isAfterLast()) {
			setItem(item, c);
	    }
		c.close();
		if (db.isOpen()) db.close();
		return item;
	}
	
	private DatabaseUtil(Context context, String databaseName){
		this.databaseHelper = new DatabaseHelper(context, databaseName, null, Settings.DATABASEVERSION);
	}
	
	public static DatabaseUtil initDatabase (Context context) {
		if (databaseUtil == null) {
			databaseUtil = new DatabaseUtil(context, Settings.DATABASENAME);
		}
		return databaseUtil;
	}
	
	public void closeDatabase () {
		if(db.isOpen()) db.close();
	}
	
	private void setItem (RSSItem item, Cursor c) {
		RSSItemInterface itemInterface;
		RSSItemInterfaceFactoryMethod itemFactory = new RSSItemInterfaceFactoryXmlPull();
		
		item.setId(c.getInt(0));
		itemInterface = itemFactory.createRSSItem("title");
		itemInterface.setContent(c.getString(1));
		item.setTitle((RSSItemTitle) itemInterface);
		itemInterface = itemFactory.createRSSItem("description");
		itemInterface.setContent(c.getString(2));
		item.setDescription((RSSItemDescription) itemInterface);
		itemInterface = itemFactory.createRSSItem("pubDate");
		itemInterface.setContent(c.getString(3));
		item.setPubdate((RSSItemPubdate) itemInterface);
		itemInterface = itemFactory.createRSSItem("author");
		itemInterface.setContent(c.getString(4));
		item.setAuthor((RSSItemAuthor) itemInterface);
		itemInterface = itemFactory.createRSSItem("link");
		itemInterface.setContent(c.getString(5));
		item.setLink((RSSItemLink) itemInterface);
		item.setFeedid(c.getInt(6));
	}
	
	public void insertItem(RSSItem item, int feedid) {
		if (db == null || !db.isOpen() || db.isReadOnly()) db = databaseHelper.getWritableDatabase();
		ContentValues cv=new ContentValues();
    	if (item.getTitle() != null) cv.put("_title", item.getTitle().getContent());
    	if (item.getDescription() != null) cv.put("_description", item.getDescription().getContent());
    	if (item.getPubdate() != null) cv.put("_pubdate", item.getPubdate().getContent());
    	if (item.getAuthor() != null) cv.put("_author", item.getAuthor().getContent());
    	if (item.getLink() != null) cv.put("_link", item.getLink().getContent());
    	if (feedid != 0) cv.put("_feedid", feedid);
    	db.insert("item", null, cv);
    	if (db.isOpen()) db.close();
	}
	
	public void updateFeedPubdate(String pubdate, int feedid) {
		if (db == null || !db.isOpen() || db.isReadOnly()) db = databaseHelper.getWritableDatabase();
		ContentValues cv=new ContentValues();
    	cv.put("_pubdate", pubdate);
    	db.update("feed", cv, "_id=?", new String[]{String.valueOf(feedid)});
    	if (db.isOpen()) db.close();
	}
	
	public int updateFeed (String title, String pubdate, int feedid) {
		if (db == null || !db.isOpen() || db.isReadOnly()) db = databaseHelper.getWritableDatabase();
		ContentValues cv=new ContentValues();
		cv.put("_title", title);
		cv.put("_pubdate", pubdate);
		int temp = db.update("feed", cv, "_id = ?", new String[]{String.valueOf(feedid)});
		if (db.isOpen()) db.close();
		return temp;
	}
	
	public int updateFeedTitle (String title, int feedid) {
		if (db == null || !db.isOpen() || db.isReadOnly()) db = databaseHelper.getWritableDatabase();
		ContentValues cv=new ContentValues();
		cv.put("_title", title);
		int temp = db.update("feed", cv, "_id = ?", new String[]{String.valueOf(feedid)});
		if (db.isOpen()) db.close();
		return temp;
	}
	
	public int deleteItems (int feedid) {
		if (db == null || !db.isOpen() || db.isReadOnly()) db = databaseHelper.getWritableDatabase();
		int temp = db.delete("item", "_feedid = ?", new String[]{String.valueOf(feedid)});
		if (db.isOpen()) db.close();
		return temp;
		
	}
}
